package fr.uniscol.dao.impl;

import fr.uniscol.dao.IEleveDao;
import fr.uniscol.dao.IOptionDao;
import fr.uniscol.dao.UniscolDaoSupport;
import static fr.uniscol.dao.impl.SQLNames.*;
import fr.uniscol.dao.mapper.EleveRowMapper;
import fr.uniscol.dao.mapper.ScolariteRowMapper;
import fr.uniscol.model.BaseAnneeScolaire;
import fr.uniscol.model.Classe;
import fr.uniscol.model.Eleve;
import fr.uniscol.model.Scolarite;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository("eleveDao")
public class EleveDaoImpl extends UniscolDaoSupport implements IEleveDao {

    public static final DateFormat MYSQL_DATEFORMAT = new SimpleDateFormat("dd-MM-yyyy");
    
    @Autowired
    private IOptionDao optionDao;
    
	@Override
	public boolean saveEleve(Eleve eleve) {
		
		String req = "";
		Object[] params = null;
		int rowAffected = 0;
		
		if(eleve.getId() == 0){
			
			if(!eleve.getIdSiecle().equals("")){
				// Synchro
				req = "UPDATE " + TABLE_ELEVE + " SET nom=?,prenom=?,ine=? WHERE etablissementid=? AND annee=? AND id_siecle=? ";
				params = new Object[]{
					eleve.getNom(),
					eleve.getPrenom(),
					eleve.getIne(),
					eleve.getBase().getEtablissement().getId(),
					eleve.getBase().getAnnee(),
					eleve.getIdSiecle()
				};
				
				rowAffected = getJdbcTemplate().update(req, params);
			}
			
			if(rowAffected == 0){
				// Insert
				req = "INSERT INTO " + TABLE_ELEVE + " (baseid, nom, prenom, ine, id_siecle) VALUES(?,?,?,?,?) ";
				params = new Object[]{
					eleve.getBase().getId(),
					eleve.getNom(),
					eleve.getPrenom(),
					eleve.getIne(),
					eleve.getIdSiecle()
				};
				
				rowAffected = getJdbcTemplate().update(req, params);
			}
			
		}else{
			// UPDATE
			req = "UPDATE " + TABLE_ELEVE + " SET nom=?,prenom=?,ine=? WHERE eleveid=? ";
			params = new Object[]{
				eleve.getNom(),
				eleve.getPrenom(),
				eleve.getIne(),
				eleve.getId()
			};
			
			rowAffected = getJdbcTemplate().update(req, params);
		}
		
		// Supprime structures
		return rowAffected == 1;
	}
	
	@Override
	public long saveScolariteEleve(Eleve eleve, Scolarite scolarite){
		
        long scolariteId = 0;
        
        String reqUpdate = "UPDATE " + TABLE_SCOLARITE_ELEVE;
        reqUpdate += " SET mefid=?,structureid=?";
        if(scolarite.getPeriode().getDebut() != null){
            reqUpdate += ",debut=STR_TO_DATE(?, '%e-%c-%Y')";
        }else{
            reqUpdate += ",debut=?";
        }
        if(scolarite.getPeriode().getFin() != null){
            reqUpdate += ",fin=STR_TO_DATE(?, '%e-%c-%Y')";
        }else{
            reqUpdate += ",fin=?";
        }
        reqUpdate += " WHERE scolariteid=? ";
        
		int rowAffected = getJdbcTemplate().update(reqUpdate, new Object[]{
			scolarite.getMef() != null ? scolarite.getMef().getId() : null,
			scolarite.getClasse() != null ? scolarite.getClasse().getId() : null,
            (scolarite.getPeriode().getDebut() != null ? scolarite.getPeriode().getDebutFormated(MYSQL_DATEFORMAT) : null),
            (scolarite.getPeriode().getFin() != null ? scolarite.getPeriode().getFinFormated(MYSQL_DATEFORMAT) : null),
            scolarite.getId()
		});
        
        scolariteId = scolarite.getId();
		
		if(rowAffected == 0){
			// Insert into
            String reqInsert = "INSERT INTO " + TABLE_SCOLARITE_ELEVE + " (eleveid, mefid, structureid, actuelle,debut,fin) ";
            reqInsert += "VALUES(?,?,?, 1";
            if(scolarite.getPeriode().getDebut() != null){
                reqInsert += ",STR_TO_DATE(?, '%e-%c-%Y')";
            }else{
                reqInsert += ",?";
            }
            if(scolarite.getPeriode().getFin() != null){
                reqInsert += ",STR_TO_DATE(?, '%e-%c-%Y')";
            }else{
                reqInsert += ",?";
            }
            reqInsert += ")";
            
			rowAffected = getJdbcTemplate().update(reqInsert, new Object[]{
				eleve.getId(),
				scolarite.getMef() != null ? scolarite.getMef().getId() : null,
				scolarite.getClasse() != null ? scolarite.getClasse().getId() : null,
                (scolarite.getPeriode().getDebutFormated(MYSQL_DATEFORMAT)),
                (scolarite.getPeriode().getFin() == null ? null : scolarite.getPeriode().getFinFormated(MYSQL_DATEFORMAT))
			});
            
            scolariteId = getJdbcTemplate().queryForObject("SELECT LAST_INSERT_ID()", Long.class);
		}
		return scolariteId;
	}

	@Override
	public List<Eleve> getAllEleves(BaseAnneeScolaire base) {
		
		List<Eleve> eleves = getJdbcTemplate().query("SELECT * FROM " + TABLE_ELEVE + " WHERE baseid=? ", 
				new Object[]{
						base.getId(), 
					}, 
				new EleveRowMapper(base));
		
		return eleves;
	}
	
	@Override
	public List<Scolarite> getAllScolaritesActuelles(BaseAnneeScolaire base){
		
		List<Scolarite> scolarites = getJdbcTemplate().query("SELECT s.*, cl.libelle, m.libelle_long " +
															 "FROM " + TABLE_ELEVE + " e, " + TABLE_SCOLARITE_ELEVE + " s, " + TABLE_STRUCTURE+ " cl, " + TABLE_MEF + " m " +
															 "WHERE e.scolariteactuelleid=s.scolariteid AND cl.baseid=? " +															 
															 "AND s.structureid=cl.structureid " + 
                                                             "AND m.mefid=s.mefid"            ,
															 new Object[]{
			base.getId(),
		}, new ScolariteRowMapper());
		
		return scolarites;
	}

	@Override
	public List<Eleve> getAllElevesOfClasse(Classe classe) {
		
		List<Eleve> eleves = getJdbcTemplate().query("SELECT * " +
													 "FROM " + TABLE_ELEVE + " e, " + TABLE_SCOLARITE_ELEVE + " sco " +
													 "WHERE e.scolariteactuelleid=sco.scolariteid " +
													 "AND sco.structureid = ? " + 
                                                     "", new Object[]{
			classe.getId(),
		}, new EleveRowMapper(classe.getBase()));
		
		return eleves;
	}

	@Override
	public Eleve getEleveById(long eleveId) {
		
		Eleve eleve = (Eleve) getJdbcTemplate().queryForObject("SELECT * FROM " + TABLE_ELEVE + " WHERE eleveid=? ", new Object[]{
			eleveId
		}, new EleveRowMapper());
		
		return eleve;
	}

	@Override
	public List<Scolarite> getAllScolaritesOfEleve(Eleve eleve) {
		
		List<Scolarite> scolarites = getJdbcTemplate().query("SELECT * "
                                                                    + "FROM " + TABLE_SCOLARITE_ELEVE + " sco, " + TABLE_STRUCTURE+ " cl, " + TABLE_MEF + " m "
                                                                    + "WHERE sco.eleveid=? "
                                                                    + "AND sco.structureid=cl.structureid "
                                                                    + "AND m.mefid=sco.mefid ", new Object[]{
			eleve.getId()
		}, new ScolariteRowMapper());
		
		return scolarites;
	}

    @Override
    public boolean deleteScolariteEleve(Scolarite scolarite) {
        
        int nbRowsAffected = getJdbcTemplate().update("DELETE FROM " + TABLE_SCOLARITE_ELEVE + " WHERE scolariteid=? ", new Object[]{
           scolarite.getId()
        });
        
        return nbRowsAffected == 1;
    }
}
